package com.vsoft.anti_fra;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import org.apache.log4j.Logger;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import com.vsoft.framework.core.BaseException;
import com.vsoft.framework.core.BusiObj;
import com.vsoft.framework.core.Configuration;
import com.vsoft.framework.core.IService;
import com.vsoft.framework.core.db.OperAgent;
import com.vsoft.framework.core.db.SQLArgs;
import com.vsoft.framework.core.esb.ServiceBus;
import com.vsoft.framework.core.util.BOHelper;
import com.vsoft.framework.core.util.LogCollect;

public class FileExport implements IService{
	private static Logger	vLogger		= Logger.getLogger(FileExport.class);
	private Connection conn;
	private String				vName			= FileExport.class.getName(); 
	private static SQLArgs				vArg			= new SQLArgs(); 
	private String g_sql;
	private ArrayList tableHeader;
	private ArrayList tableData;
	private int pageSize  			            = 500; //每页多少行
	private int sheetSize						=30000;
	private int pageIndex 						= 1; //当前页号
	private  String maxPageNum      			= "20";//每次导出多少页
	
	private  String resultName              = "RESULT";
	private	String	colHeader				=	"COLHEADER",pageRows="500";
	private	HashMap<String,Object>	hColHeader	=	new HashMap<String,Object>();//字段对照
	private	int	rowCounts=0;
 	ByteArrayOutputStream os              = null;
	WritableWorkbook wwb                   = null;
	WritableSheet wsheet                       = null;
	  
	public BusiObj execute(BusiObj sourceObj) throws BaseException{
		String serviceName 						= sourceObj.getName();
		conn 											= sourceObj.getConnection();
		 
		long a 											= System.currentTimeMillis();
                 
		if(serviceName.equals("AF_EXPORT_XLS")) { 
			this.ExportXLS(sourceObj);
		}else if(serviceName.equals("EXPORT_DELETE")) {
			this.EXPORT_DELETE(sourceObj) ;
		}
		else {
			throw new BaseException(vName + "-001", serviceName  + "没有匹配服务");
		}
		
		System.out.print(serviceName+"_USED_TIME_LONG:"+String.valueOf(System.currentTimeMillis()-a) );
		return sourceObj;
	}
    public static String getSysParam(String key,Connection conn)throws BaseException
    {
    	String	value="";
    	vArg.reset();
    	String sql	=	"select t.param_val param_val from system_params t  where t.param_name = :param_name  ";
    	vArg.setArg("param_name", key);
    	
		BusiObj bo= OperAgent.execQuery(conn, "getSysParam", sql, vArg);
		if(bo.getBusiObjList("RESULT").size()>0)
		{
			value	=(String)bo.getBusiObjList("RESULT").get(0).getRawData().get("param_val".toUpperCase());
			
		}

		
	
		//String	value	=	bo.getString("param_val");
		return value;
    }
    public static boolean isAEmptyString(String str)
    {
    	if(str==null  ||  str.trim().equals(""))
    		return true;
    	else
    		return false;
    }

	public void  ExportXLS(BusiObj sourceObj) throws BaseException {
		String fileDir           						= "";
		String fileName          					= "";
		String filePath          						= "";
		BusiObj callBO          					= new BusiObj();
		BusiObj paramsObj        				= new BusiObj();
		BusiObj dataBO        					= new BusiObj();
 	 
		OutputStream fos        			 	= null; 
//		fileDir = ConfigurationMgr.instance().getString("WebRoot", "E:");//TODO
//		if(fileDir.equals("")) {
//			fileDir = SysParam.getHomePathString();
//		}
		fileDir										=	FileExport.getSysParam("downDir", conn);
		if(FileExport.isAEmptyString(fileDir))
			fileDir 											=	Configuration.getHomePath();
		
		fileName 										= "exportxls"+ String.valueOf( System.currentTimeMillis())   ; 
		if (fileDir.lastIndexOf("/") == (fileDir.length()-1))
		{
			filePath 									= fileDir   + fileName;
		}
		else
		{
			filePath 									= fileDir  //+"/" + fileName
			;
		}


		paramsObj.setString ("PAGE_INDEX", "1" );
		paramsObj.setString("COUNT", "0");
		//paramsObj.setString("PAGE_SIZE", "500");
		///////////////////////////导出excel的准备/////////////////////////////
		BusiObj 	colHeaderBO				=	sourceObj.getBusiObj(colHeader);//获取前台传入的字段对照信息
		colHeaderBO.setName(colHeader);
		////////////////////////////////////////////////////////		
		 
		 //callBO.setValue("colHeader", colHeaderBO);
		 this.setHeader(colHeaderBO);//设置excel文件的字段对照信息
		 paramsObj									=	sourceObj.getBusiObj("PARAMS");//获取查询服务的参数信息
		 callBO.setValue("PARAMS", paramsObj);
		 paramsObj.setConnection(sourceObj.getConnection());
		 dataBO										=	this.getExpData(paramsObj);//查询需要导出的数据
		 callBO.setValue("expData", dataBO);//将需要导出的数据传入excel
		///////////////////////////导出excel的准备完成/////////////////////////////
		
		callBO.setString("MAX_PAGE_NUM", maxPageNum);
		callBO.setString("FILE_NAME", fileName);//+".xls"
		callBO.setString("FILE_DIR", filePath);//+".xls"
		callBO.setName("exp2Excel");
		//1.导出成一个一个的EXCEL文件
		int fileCount = this.doExportFile(callBO);
		//2.压缩成.zip文件.
		sourceObj.setString("FILE_NAME", fileName+".zip");
		sourceObj.setString("FILE_DIR", filePath+".zip");
		//this.zip(fileDir,fileName, ".xls", fileCount, true);//
		//3.另存为
		String url										=	FileExport.getSysParam("downUrl", conn);
		sourceObj.setString("url", url+"/"+fileName+"_0.xls");
		//sourceObj.setString("url", url+"/"+fileName+".zip");
		sourceObj.setValue("ResultFlag", "0");
 	}
	public static int string2intForBO(String fieldName,BusiObj vInBO)
	{
		int	iVal										=	0;
		try{
			String fieldValue =vInBO.getString(fieldName,"");
			if(!fieldValue.trim().equals(""))
				iVal=Integer.parseInt(fieldValue);
			else
				iVal										=	0;
		}
		catch(Exception e)
		{
			e.printStackTrace();
			iVal											=	0;
		}
		return iVal;
	}

	private BusiObj getExpData(BusiObj srcBO) throws BaseException
	{
		BusiObj		dataBO			=		new BusiObj();
		//try
		{
			//paramsBO	=				(BusiObj) srcBO.getValue("PARAMS");
			String	total				=		"totalRows";
			//srcBO.setConnection(srcBO.getConnection());
			int	totalRows			=		FileExport.string2intForBO(total, srcBO);
			if(totalRows<=0)
				srcBO.setString(total, pageRows);
			srcBO.setName(srcBO.getString("service_name"));
			dataBO						=		ServiceBus.runService(srcBO);
		}
/*		catch(Exception e)
		{
			 vLogger.debug(LogCollect.getStackLog(e));
			
			e.printStackTrace();
			throw new BaseException("","");
		}*/
		return dataBO;
	}
	   public void zip(String fileDir,String fileName,String Extension, int fileCount,boolean isDelete){
		String zipname      		=  fileDir+"/"+fileName+".zip" ;
		 
	    String [] files =fileList(fileName, Extension,fileCount) ;
	    if(isNeedZip(files))
	    {
		    zipFiles(fileDir,files,zipname);
		    for (int i = 0; i < files.length && isDelete; i++) {
		        File f 					= new File(fileDir+"/"+files[i]);
		        f.delete();
		     }
	    }
	   }
	   private boolean isNeedZip(String [] files)
	   {
		   boolean flag	=	false;
		   if(	!	(files==null||
				   (files.length==1&&files[0].trim().equals(""))))
			   		flag	=	true;
		   return flag;
	   }

	   private String [] fileList (String fileName, String Extension ,int fileCount )
	   {
		   String fileList     = "";
		    for(int i=0;i<fileCount;i++){
		    	fileList += fileName +"_" + String.valueOf(i) +Extension+";";
		    }
		    String [] files = fileList.split("\\;");
		   return  files;
	   }
	   
	   /**
	    * 压缩某路径下所有文件
	    * @param filePath 要压缩的路径
	    * @param zipname 压缩后的路径（含压缩后文件名 如：d:\\tt.zip）
	    */
	   private void zipFiles (String fileDir,String [] files,String zipname){
	    FileOutputStream fos=null;
	       ZipOutputStream zipOut=null;
	       try {
 
	    	   File zipFile =new File (zipname);
 	    	   fos = new FileOutputStream(zipname);//创建文件输出流（低级流）
	           zipOut = new ZipOutputStream(fos);//创建zip文件输出流
	           
	           int i = 0;

	           for (i = 0; i < files.length; i++) {
	        	   
	 
	               File f = new File(files[i]);
	               FileInputStream fis = new FileInputStream(fileDir+"/"+files[i]);
	               ZipEntry ze = new ZipEntry(f.getName());
	               zipOut.putNextEntry(ze);
	               int c = 0;
	               while ((c = fis.read()) != -1) {
	                   zipOut.write(c);
	               }
	               fis.close();

	           }
	           zipOut.close();
	       } catch (FileNotFoundException ex) {
	           ex.printStackTrace();
	       } catch (IOException ex) {
	           ex.printStackTrace();
	       }

	   }
	   public void setHeader(BusiObj	colHeaderBO)throws BaseException
	   {
/*		   ArrayList alColHeader	=	new	ArrayList();
		   String	colHeader			=	"COLHEADER";*/
		   try
		   {
			   hColHeader=colHeaderBO.getRawData();
			   //hColHeader.size();
/*			   alColHeader	=	colHeaderBO.getBusiObjList(colHeader);
			   this.setHeader(alColHeader);*/
		   }
		   catch(Exception e)
		   {
			   e.printStackTrace();
			   throw new BaseException("","");
		   }
		   return ;
	   }
	  public void setHeader(ArrayList tableHeader) {
	      this.tableHeader = tableHeader;
	    }

	    public void setData(ArrayList tableData) {
	      //this.tableData = tableData;
	    	if (this.tableData != null){
		        this.tableData.clear();
		        this.tableData = null;
			}
			this.tableData = tableData;
	    }
	    
	    public void setResultName(String resultName) {
	        //this.tableData = tableData;
	      	if (this.resultName.length() >0){
	  	        this.resultName = "";
	  		}
	  		this.resultName = resultName;
	      }
	    
	    public void setPageInfo(BusiObj sourceObj)throws BaseException {
	    	 
	    	/**
			 * 设置取数据的参数
			 */
	    	setpageIndex(pageIndex + 1);
 			sourceObj.setString  ("PAGE_INDEX",        String.valueOf( pageIndex ) );
			sourceObj.setString   ("PAGE_SIZE",         String.valueOf( pageSize  ) );
			sourceObj.setString   ("COUNT",             "0");
	      }
	    
	    public void setpageIndex(int pageIndex) {
	    	this.pageIndex = pageIndex;
	    	 
	      }
	    public void setPageSize(int pageSize) {
	    	this.pageSize = pageSize;
	    	 
	      }

	    public String getMimeType()
	    {
	        return "application/vnd.ms-excel"; //$NON-NLS-1$
	    }

	  

	   
	    protected String getRowEnd()
	    {
	        return "\n"; //$NON-NLS-1$
	    }
 
	    protected String getCellEnd()
	    {
	        return "\t"; //$NON-NLS-1$
	    }

	    
	    protected boolean getAlwaysAppendCellEnd()
	    {
	        return false;
	    }

	    
	    protected boolean getAlwaysAppendRowEnd()
	    {
	        return false;
	    }

	private void writeExcelColName() throws BaseException
	{
		try
		{
			int index=0;
	    	for(String key:this.hColHeader.keySet())
	    	{
	    		String field_value=(String)hColHeader.get(key);
	            if(field_value==null)
	            	field_value="";
	            Label label = new Label(index, 0, field_value);
	            wsheet.addCell(label);
	            index++;
	    	}
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new BaseException(this.vName+"-005","导出excel时设置列标题不成功");
		}
	}
	private void wirteExcelRow(HashMap<String,Object> rowData,int rowNum )throws BaseException
	{
		try
		{
			int index=0;
	    	for(String key:this.hColHeader.keySet())
	    	{
	    		String field_value=(String)rowData.get(key);
	            if(field_value==null)
	            	field_value="";
	            Label label = new Label(index, rowNum+1, field_value);
	            wsheet.addCell(label);
	            index++;
	    	}
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new BaseException(this.vName+"-002","导出excel时，写入第"+rowNum+"行数据不成功");
		}
	}

	public int doExportFile ( BusiObj sourceObj ) throws BaseException 
	    {
	    	int size                     = 1 ;
	    	int iRows                  = 0 ;
	    	int iSheetRows			=	0;
	    	int i                        	= 0 ;
	    	String filePath          = "";
	    	String fileName        = (String) sourceObj.getString("FILE_NAME");//无扩展名.
	    	String fileDir             = (String) sourceObj.getString("FILE_DIR");//无扩展名.
	    	int maxPageNum     = 20;
	    	
	    	BusiObj		dataBO		=	new BusiObj();
	    	try 
	    	{
	    		maxPageNum      =  Integer.parseInt((String) sourceObj.getString("MAX_PAGE_NUM"));
	    		if (maxPageNum < 1)
	    		{
	    			maxPageNum = 20;
	    		}
	    	}catch(Exception e)
	        {
	    		
	        }
	    	
	    	OutputStream out             = null;
	    	BusiObj callObj             = new BusiObj();
	    	ArrayList dataInfo           = new ArrayList(); 
	    	ArrayList<BusiObj> rows           = new ArrayList<BusiObj>();
	     	try
			{
	     		for (i=0;  size >0;i++ )//这个是总控 ,可以加上限制.
	    		{
	     			filePath 	= fileDir+"/"+fileName+"_"+String.valueOf(i)+".xls";//excel文件名
	     			out      		= new FileOutputStream(filePath,true);  
	                wwb      	= Workbook.createWorkbook(out);//创建workbook
	                wsheet   	= wwb.createSheet("export"+String.valueOf(i)  , 0 );//创建sheet
	                iRows    	= 0;  
	                callObj		=	sourceObj.getBusiObj("expData");//获取导出数据
	                HashMap<String,Object> rowData	=	new HashMap<String,Object> ();
	                rows			=	callObj.getBusiObjList(resultName);//获取记录行集合
	                size			=	rows.size();//获取导出数据记录数
	                
	                ///////////////////开始写入excel文件/////////////////////
	                //1、写入列标题
	                this.writeExcelColName();
	                //2、写入每一行数据
	                
	                for(BusiObj row:rows)
	                {
	                	rowData=row.getRawData();
	                	this.wirteExcelRow(rowData,iSheetRows );//iRows
	                	iRows++;
	                	iSheetRows++;
	   		    	    //System.out.println("pageIndex"+String.valueOf( pageIndex ));
	   		    		//java.lang.Runtime.getRuntime().runFinalization();
	   		    		if(iSheetRows>=this.sheetSize)
	   		    		{
	   		    			//vLogger.info("========================export"+String.valueOf(i)+"\r\n iRows"+iRows+"\r\n iSheetRows"+iSheetRows);
	   		    			iSheetRows	=	0;
	   		    			i++;
	   		    			java.lang.Runtime.getRuntime().runFinalization();
	   		    			wsheet   	= wwb.createSheet("export"+String.valueOf(i)  , i );//创建sheet
	   		    			this.writeExcelColName();
	   		    		}
	                }
	    			if ( iRows >0 ) 
	    			{
	    				//vLogger.info("write xls========================export"+String.valueOf(i)+"\r\n iRows"+iRows+"\r\n iSheetRows"+iSheetRows);
	    				wwb.write();
	    			}
	    			
	         		wwb.close();
	         		wwb = null;
	         		out.close();
	         		out = null;
	         		size=0;
	                
/*	     			for (int sheetInt =0; sheetInt <maxPageNum && size >0;sheetInt++)//每个sheet.追加一次文件.
	   		    	{
	     				this.setPageInfo(sourceObj);

	     				
	     				//callObj.setName(sourceObj.getName())  ;
	     				 //size=callObj.getBusiObjList(resultName).size();
	     				
	     				
	     				
//	   		    	    size = callDict.getCountByName (resultName) ;//TODO
	   		    		if (size ==0) break;
	   		    		
	   		    		dataInfo = this.conversionResult(callObj);//设置数据
	   		    		setData(dataInfo);
 	   		    		generateRows(iRows); //生成SHEET rows
 	   		    		callObj  = null;
	   		    		dataInfo = null;
	   		    		iRows +=size;   
	   		    	    System.out.println("pageIndex"+String.valueOf( pageIndex ));
	   		    		java.lang.Runtime.getRuntime().runFinalization();
	   		    		 	       
	   		    	}
	    			if ( iRows >0 || i ==0 ) 
	    				wwb.write();
	    			
	         		wwb.close();
	         		wwb = null;
	         		out.close();
	         		out = null;
	         		*/
	      		}
	     		
	     		if (iRows == 0 && i !=0 )
				{
	     			vLogger.info("write xls========================delete"+String.valueOf(i)+"\r\n iRows"+iRows+"\r\n iSheetRows"+iSheetRows);
					i -- ; 
	     			File f=new File(filePath);
			    	 f.delete();
				}
	  	    } 
	     	catch (Throwable e1)
	        {
	     		e1.printStackTrace();
	          	vLogger.error(e1);
	          	throw new BaseException(vName + "-004","导出文件失败。");
	        }finally 
	        {
	        	try 
				{    if(wwb!=null)
						  wwb.close();
				      if(out!=null)
				    	  out.close();
					  
				}catch(Exception e) 
				{
					 e.printStackTrace();  
					 throw new BaseException(vName + "-003","导出文件后关闭资源失败。");
				}
	         }
	         return i;
	    }        
   
 
   private void generateRows(int row) throws WriteException {
	        	int k=0;
	            for (int i = row; i < row+tableData.size(); i++) {
	              HashMap dataMap = (HashMap) tableData.get(k);
	              int index=0;
	              for(String key:this.hColHeader.keySet())
	              {
		                String value = (String) dataMap.get(key);
		                if(value==null)
		                  value="";
		                Label label = new Label(index, i+1, value);
		                wsheet.addCell(label);
		                index++;
	              }

	              k++;
	            }
	       }
  

   private void EXPORT_DELETE(BusiObj sourceObj) throws BaseException {
	   
	   String fileDir =(String) sourceObj.getString("FILE_DIR");
	   if (fileDir.length() == 0)
		   return;
	   else if (fileDir.indexOf("exportxls") <0 && fileDir.indexOf("exortpdf") <0)
	   {
		   return;
	   }
	   
	   boolean   success   =   (new   File(fileDir)).delete();   
       if   (!success) 
       {    
    	   sourceObj.setString("SUCCESS", "No");
       }  
       sourceObj.setString("SUCCESS", "Yes");
	 }
}
